package zozopo.warehouse.operations;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.json.JSONArray;

import zozopo.comm.ZOZOPOCODE;
import zozopo.database.BusiDatabaseManager;
import zozopo.database.SqlMachine;
import zozopo.flow.comm.Flow;
import zozopo.flow.comm.FlowCode;
import zozopo.flow.operations.RecordlistOS;
import zozopo.flow.operations.RecordlistTB;
import zozopo.operations.comm.DictionaryOS;
import zozopo.operations.comm.OperatingService;

public class ReceiptlistOS extends OperatingService{
	
	
	public ReceiptlistOS(Connection conn){
		super(conn);
	}
	
	public ReceiptlistOS(){
		this.databaseConnection = new BusiDatabaseManager();
		this.conn = databaseConnection.getConnection();
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public List<ReceiptlistTB> selectListBySql(String sql){
		List<ReceiptlistTB> receiptlistList = null;
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		//Flow flow = new Flow(conn);
		//Map flowMap = flow.getFlowStateMapByKind(FlowCode.HOUSEWARE_RECEIPTLIST);
		if(resultSet != null){
				try {
					receiptlistList = new ArrayList<ReceiptlistTB>();
					while(resultSet.next()){
						ReceiptlistTB receiptlist = new ReceiptlistTB();
						
						receiptlist.setCustomerid_receiptlist(resultSet.getString(ReceiptlistTB.CUSTOMERID_RECEIPTLIST));
						receiptlist.setCustomerinfo_receiptlist(resultSet.getString(ReceiptlistTB.CUSTOMERINFO_RECEIPTLIST));
						receiptlist.setDate_receiptlist(resultSet.getString(ReceiptlistTB.DATE_RECEIPTLIST));
						receiptlist.setDepartment_receiptlist(resultSet.getString(ReceiptlistTB.DEPARTMENT_RECEIPTLIST));
						receiptlist.setDepartmentid_receiptlist(resultSet.getInt(ReceiptlistTB.DEPARTMENTID_RECEIPTLIST));
						receiptlist.setGoodsnumber_receiptlist(resultSet.getInt(ReceiptlistTB.GOODSNUMBER_RECEIPTLIST));
						receiptlist.setId_receiptlist(resultSet.getInt(ReceiptlistTB.ID_RECEIPTLIST));
						receiptlist.setRemark_receiptlist(resultSet.getString(ReceiptlistTB.REMARK_RECEIPTLIST));
						receiptlist.setShipprice_receiptlist(resultSet.getDouble(ReceiptlistTB.SHIPPRICE_RECEIPTLIST));
						receiptlist.setTotal_receiptlist(resultSet.getDouble(ReceiptlistTB.TOTAL_RECEIPTLIST));
						receiptlist.setProfit_receiptlist(resultSet.getDouble(ReceiptlistTB.PROFIT_RECEIPTLIST));
						receiptlist.setState_receiptlist(resultSet.getString(ReceiptlistTB.STATE_RECEIPTLIST));
						
						receiptlist.setUseremail_receiptlist(resultSet.getString(ReceiptlistTB.USEREMAIL_RECEIPTLIST));
						receiptlist.setUsername_receiptlist(resultSet.getString(ReceiptlistTB.USERNAME_RECEIPTLIST));
						
						receiptlist.setShipno_receiptlist(resultSet.getString(ReceiptlistTB.SHIPNO_RECEIPTLIST));
						receiptlist.setRealshipprice_receiptlist(resultSet.getDouble(ReceiptlistTB.REALSHIPPRICE_RECEIPTLIST));
						receiptlistList.add(receiptlist);
					}
					resultSet.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		
		return receiptlistList;
	
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public boolean insertReceiptlist(ReceiptlistTB receiptlist){
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> insertMap = new HashMap<String, Object>();
		
		insertMap.put(ReceiptlistTB.CUSTOMERID_RECEIPTLIST, receiptlist.getCustomerid_receiptlist());
		insertMap.put(ReceiptlistTB.CUSTOMERINFO_RECEIPTLIST, receiptlist.getCustomerinfo_receiptlist());
		insertMap.put(ReceiptlistTB.DEPARTMENT_RECEIPTLIST, receiptlist.getDepartment_receiptlist());
		insertMap.put(ReceiptlistTB.DEPARTMENTID_RECEIPTLIST, receiptlist.getDepartmentid_receiptlist());
		
		insertMap.put(ReceiptlistTB.REMARK_RECEIPTLIST, receiptlist.getRemark_receiptlist());
		insertMap.put(ReceiptlistTB.USEREMAIL_RECEIPTLIST, receiptlist.getUseremail_receiptlist());
		insertMap.put(ReceiptlistTB.USERNAME_RECEIPTLIST, receiptlist.getUsername_receiptlist());
		insertMap.put(ReceiptlistTB.GOODSNUMBER_RECEIPTLIST, receiptlist.getGoodsnumber_receiptlist());
		insertMap.put(ReceiptlistTB.SHIPPRICE_RECEIPTLIST, receiptlist.getShipprice_receiptlist());
		insertMap.put(ReceiptlistTB.TOTAL_RECEIPTLIST, receiptlist.getTotal_receiptlist());
		
		insertMap.put(ReceiptlistTB.SHIPNO_RECEIPTLIST, receiptlist.getShipno_receiptlist());
	
		
		return sqlMachine.insertRow(ReceiptlistTB.TABLE_NAME, insertMap);
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public boolean upadteForProfit(ReceiptlistTB receiptlist,double realShipPrice){
		
		String sql = "SELECT SUM(profit_receipt) AS profit_receipt,receiptlistid_receipt FROM receipt_warehouse "
			+ " WHERE receiptlistid_receipt=" + receiptlist.getId_receiptlist();
		
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		
		
		if(resultSet != null){
				try {
					while(resultSet.next()){
						receiptlist.setProfit_receiptlist(resultSet.getDouble(ReceiptTB.PROFIT_RECEIPT));
					}
					resultSet.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		receiptlist.setProfit_receiptlist(receiptlist.getProfit_receiptlist() + (receiptlist.getShipprice_receiptlist()-realShipPrice));
		
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> updateMap = new HashMap<String, Object>();
		
		updateMap.put(ReceiptlistTB.PROFIT_RECEIPTLIST, receiptlist.getProfit_receiptlist());
		updateMap.put(ReceiptlistTB.REALSHIPPRICE_RECEIPTLIST, realShipPrice);
		//insertMap.put(supplierTB.DATE_supplier, goods.getUnit_goods());
		
		return sqlMachine.updateRow(ReceiptlistTB.TABLE_NAME, updateMap, ReceiptlistTB.ID_RECEIPTLIST, receiptlist.getId_receiptlist());
	}
	
	
	public List<ReceiptlistTB> selectReceiptlist(int beginPageNum,int pageSize){
		
		List<ReceiptlistTB> receiptlistList = null;
		String sql = "SELECT * FROM " + ReceiptlistTB.TABLE_NAME  
		+ " ORDER BY " + ReceiptlistTB.DATE_RECEIPTLIST + " DESC "
		+ " limit "+beginPageNum+","+pageSize; 
		receiptlistList = selectListBySql(sql);
		
		return receiptlistList;
	}
	
	public List<ReceiptlistTB> selectForReport(int beginPageNum,int pageSize){
		
		List<ReceiptlistTB> receiptlistList = null;
		
		String sql = "SELECT * FROM "+ ReceiptlistTB.TABLE_NAME 
			+ " WHERE " +ReceiptlistTB.ID_RECEIPTLIST 
			+ " IN (" 
			+ " SELECT "+RecordlistTB.KINDID_RECORDLIST+" FROM "+RecordlistTB.TABLE_NAME
			+ " WHERE "+RecordlistTB.KIND_RECORDLIST +" = 'HOUSEWARE.RECEIPTLIST'  " 
			
			+ " AND " + RecordlistTB.NEXTVALUE_RECORDLIST+ "=-1"
			+ " AND " + RecordlistTB.FLOWFLAG_RECORDLIST+ "!=9"
			//+ " limit "+beginPageNum+","+pageSize 
			+ ") "
			+ " ORDER BY " + ReceiptlistTB.DATE_RECEIPTLIST + " DESC "
			+ " limit "+beginPageNum+","+pageSize; 
		
		receiptlistList = selectListBySql(sql);
		
		return receiptlistList;
	}
	
	public List<ReceiptlistTB> selectForReport(double profitValue,int beginPageNum,int pageSize){
		String profitStr = "" ;
		if(profitValue == 0){
			
		}else if(profitValue < 0){
			profitStr = " AND " + ReceiptlistTB.PROFIT_RECEIPTLIST + "<="+profitValue;
		}else{
			profitStr = " AND " + ReceiptlistTB.PROFIT_RECEIPTLIST + ">="+profitValue;
		}
		
		
		
		List<ReceiptlistTB> receiptlistList = null;
		
		String sql = "SELECT * FROM "+ ReceiptlistTB.TABLE_NAME 
			+ " WHERE " +ReceiptlistTB.ID_RECEIPTLIST 
			+ " IN (" 
			+ " SELECT "+RecordlistTB.KINDID_RECORDLIST+" FROM "+RecordlistTB.TABLE_NAME
			+ " WHERE "+RecordlistTB.KIND_RECORDLIST +" = 'FLOW.WAREHOUSE.RECEIPTLIST'  " 
			
			+ " AND " + RecordlistTB.NEXTVALUE_RECORDLIST+ "=-1"
			+ " AND " + RecordlistTB.FLOWFLAG_RECORDLIST+ "!=9"
			//+ " limit "+beginPageNum+","+pageSize 
			+ ") "
			+ profitStr
			
			+ " ORDER BY " + ReceiptlistTB.DATE_RECEIPTLIST + " DESC "
			+ " limit "+beginPageNum+","+pageSize; 
		
		receiptlistList = selectListBySql(sql);
		
		return receiptlistList;
	}
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNumForReport(double profitValue){
		
		String profitStr = "" ;
		if(profitValue == 0){
			
		}else if(profitValue < 0){
			profitStr = " AND " + ReceiptlistTB.PROFIT_RECEIPTLIST + "<="+profitValue;
		}else{
			profitStr = " AND " + ReceiptlistTB.PROFIT_RECEIPTLIST + ">="+profitValue;
		}
		
		int rowNum = 0;
		String sql = "select count(*) as rowNum from " + ReceiptlistTB.TABLE_NAME 
			+ " WHERE " +ReceiptlistTB.ID_RECEIPTLIST 
			+ " IN (" 
			+ " SELECT "+RecordlistTB.KINDID_RECORDLIST+" FROM "+RecordlistTB.TABLE_NAME
			+ " WHERE "+RecordlistTB.KIND_RECORDLIST +" = 'HOUSEWARE.RECEIPTLIST' AND " +RecordlistTB.NEXTVALUE_RECORDLIST+ "=-1)"
			+ profitStr;
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNumForReport(){
		
		int rowNum = 0;
		String sql = "select count(*) as rowNum from " + ReceiptlistTB.TABLE_NAME 
			+ " WHERE " +ReceiptlistTB.ID_RECEIPTLIST 
			+ " IN (" 
			+ " SELECT "+RecordlistTB.KINDID_RECORDLIST+" FROM "+RecordlistTB.TABLE_NAME
			+ " WHERE "+RecordlistTB.KIND_RECORDLIST +" = 'HOUSEWARE.RECEIPTLIST' AND " +RecordlistTB.NEXTVALUE_RECORDLIST+ "=-1)";
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
	public List<ReceiptlistTB> selectAll(){
		
		List<ReceiptlistTB> receiptlistList = null;
		String sql = "SELECT * FROM " + ReceiptlistTB.TABLE_NAME ; 
		receiptlistList = selectListBySql(sql);
		
		return receiptlistList;
	}
	
	public Map<Integer,ReceiptlistTB> selectReceiptlistMap(List<RecordlistTB> recordlistList){
		
		if(recordlistList == null){
			return null;
		}else if(recordlistList.size() == 0){
			return null;
		}
		
		Map<Integer,ReceiptlistTB> receiptlistMap = new HashMap<Integer,ReceiptlistTB>();
		List<ReceiptlistTB> receiptlistList = null;
		
		String recordlistIdStr = "(";
		
		boolean ifFirst = true;
		for(RecordlistTB recordlist : recordlistList){
			if(ifFirst){
				recordlistIdStr += recordlist.getKindid_recordlist() ;
				ifFirst = false;
			}else{
				recordlistIdStr += ","+recordlist.getKindid_recordlist() ;
			}
			
		}
		recordlistIdStr += ")";
		
		String sql = "SELECT * FROM " + ReceiptlistTB.TABLE_NAME  
		+ " WHERE " + ReceiptlistTB.ID_RECEIPTLIST
		+ " IN " + recordlistIdStr; 
		
		receiptlistList = selectListBySql(sql);
		
		for(ReceiptlistTB receiptlist:receiptlistList){
			receiptlistMap.put(receiptlist.getId_receiptlist(), receiptlist);
		}
		
		return receiptlistMap;
	}
	
	public ReceiptlistTB getReceiptlistById(int id){
		String sql = "SELECT * FROM " + ReceiptlistTB.TABLE_NAME + " where " + ReceiptlistTB.ID_RECEIPTLIST + "=" + id;
		ReceiptlistTB receiptlist = new ReceiptlistTB();
		List<ReceiptlistTB> receiptlistList = selectListBySql(sql);
		receiptlist = receiptlistList.get(0);
		return receiptlist;
	}
	
	public ReceiptlistTB getReceiptlistByShipno(String shipno){
		String sql = "SELECT * FROM " + ReceiptlistTB.TABLE_NAME + " where " + ReceiptlistTB.SHIPNO_RECEIPTLIST + "='" + shipno+"'"
		+ " ORDER BY " + ReceiptlistTB.ID_RECEIPTLIST + " DESC ";
		ReceiptlistTB receiptlist = new ReceiptlistTB();
		List<ReceiptlistTB> receiptlistList = selectListBySql(sql);
		if(receiptlistList.size() == 0){
			return null;
		}
		receiptlist = receiptlistList.get(0);
		return receiptlist;
	}
	
	public ReceiptlistTB getReceiptlistByCustomerid(String customerid){
		String sql = "SELECT * FROM " + ReceiptlistTB.TABLE_NAME + " where " + ReceiptlistTB.CUSTOMERID_RECEIPTLIST + "='" + customerid+"'"
		+ " AND " + ReceiptlistTB.STATE_RECEIPTLIST +" !=9 "
		+ " ORDER BY " + ReceiptlistTB.DATE_RECEIPTLIST + " DESC ";
		ReceiptlistTB receiptlist = new ReceiptlistTB();
		List<ReceiptlistTB> receiptlistList = selectListBySql(sql);
		
		if(receiptlistList.size() == 0){
			return null;
		}else{
			receiptlist = receiptlistList.get(0);
		}
		
		return receiptlist;
	}
	
	public ReceiptlistTB getLastReceiptlistByCustomerid(String customerid){
		String sql = "SELECT * FROM " + ReceiptlistTB.TABLE_NAME + " where " + ReceiptlistTB.CUSTOMERID_RECEIPTLIST + "='" + customerid+"'"
		+ " AND " + ReceiptlistTB.STATE_RECEIPTLIST +" !=9 "
		+ " ORDER BY " + ReceiptlistTB.DATE_RECEIPTLIST + " DESC ";
		ReceiptlistTB receiptlist = new ReceiptlistTB();
		List<ReceiptlistTB> receiptlistList = selectListBySql(sql);
		
		if(receiptlistList.size() <= 1){
			return null;
		}else{
			receiptlist = receiptlistList.get(1);
		}
		
		return receiptlist;
	}
	
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public int getMaxIdByEmail(String email){
		//String sql = "select * from "+ReleaseTB.TABLE_NAME+" order by  "+ReleaseTB.RELEASE_DATE+" desc limit "+beginPageNum+","+pageSize; 
		SqlMachine sqlMachine = new SqlMachine(conn);
		int maxId = sqlMachine.getMaxId(ReceiptlistTB.ID_RECEIPTLIST, ReceiptlistTB.TABLE_NAME, ReceiptlistTB.USEREMAIL_RECEIPTLIST, email);
		return maxId;
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNum(){
		
		int rowNum = 0;
		String sql = "select count(*) as rowNum from " + ReceiptlistTB.TABLE_NAME ;
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public List<ReceiptlistTB> selectBySearchStr(String searchStr,int beginPageNum,int pageSize){
		String sql = "select * from "+ReceiptlistTB.TABLE_NAME+" WHERE  "
		+ReceiptlistTB.ID_RECEIPTLIST+" LIKE '%"+searchStr+"%'"
		+" OR "+ReceiptlistTB.CUSTOMERID_RECEIPTLIST+" LIKE '%"+searchStr+"%'"
		+" OR "+ReceiptlistTB.CUSTOMERINFO_RECEIPTLIST+" LIKE '%"+searchStr+"%'"
		
		+" OR "+ReceiptlistTB.REMARK_RECEIPTLIST+" LIKE '%"+searchStr+"%'"
		+" OR "+ReceiptlistTB.SHIPNO_RECEIPTLIST+" LIKE '%"+searchStr+"%'"
		
		+ " ORDER BY " + ReceiptlistTB.DATE_RECEIPTLIST + " DESC "
		
		+" limit "+beginPageNum+","+pageSize; 
		//ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		List<ReceiptlistTB> receiptlistList = selectListBySql(sql);
		
		return receiptlistList;
	}
	
	
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public List<ReceiptlistTB> selectBySearchStr(String searchStr,List<ReceiptTB> receiptInfoList,int beginPageNum,int pageSize){
		
		String inStr = "(";
		boolean isFirst = true;
		
		if(receiptInfoList == null){
			return null;
		}
		
		for(ReceiptTB receipt:receiptInfoList){
			if(isFirst){
				inStr += receipt.getGoodsid_receipt();
				isFirst = false;
			}else{
				inStr += "," + receipt.getGoodsid_receipt();
			}
			
		}
		inStr +=")";
		String sql = "SELECT * FROM receipt_warehouse"
				+" LEFT JOIN receiptlist_warehouse " 
				+" ON receiptlist_warehouse.id_receiptlist=receipt_warehouse.receiptlistid_receipt"
				+" WHERE goodsid_receipt IN " 
				+ inStr
				+" AND customerinfo_receiptlist" +" LIKE '%"+searchStr+"%'"
				+" GROUP BY receiptlistid_receipt HAVING COUNT(receiptlistid_receipt) >=" + receiptInfoList.size()
			+" limit "+beginPageNum+","+pageSize; 
		//ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		List<ReceiptlistTB> receiptlistList = selectListBySql(sql);
		
		return receiptlistList;
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNumForSearchStr(String searchStr,List<ReceiptTB> receiptInfoList){
		
		int rowNum = 0;
		String inStr = "(";
		boolean isFirst = true;
		
		if(receiptInfoList == null){
			return 0;
		}
		
		for(ReceiptTB receipt:receiptInfoList){
			if(isFirst){
				inStr += receipt.getGoodsid_receipt();
				isFirst = false;
			}else{
				inStr += "," + receipt.getGoodsid_receipt();
			}
			
		}
		inStr +=")";
		String sql = "SELECT * FROM receipt_warehouse"
			+" LEFT JOIN receiptlist_warehouse " 
			+" ON receiptlist_warehouse.id_receiptlist=receipt_warehouse.receiptlistid_receipt"
			+" WHERE goodsid_receipt IN " 
			+ inStr
			+" AND customerinfo_receiptlist" +" LIKE '%"+searchStr+"%'"
			+" GROUP BY receiptlistid_receipt HAVING COUNT(receiptlistid_receipt) >=" + receiptInfoList.size();
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getRow();
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNumForSearchStr(String searchStr){
		
		int rowNum = 0;
		String sql = "select count(*) as rowNum from " + ReceiptlistTB.TABLE_NAME +" WHERE  "
		+ReceiptlistTB.ID_RECEIPTLIST+" LIKE '%"+searchStr+"%'"
		+" OR "+ReceiptlistTB.CUSTOMERID_RECEIPTLIST+" LIKE '%"+searchStr+"%'"
		+" OR "+ReceiptlistTB.CUSTOMERINFO_RECEIPTLIST+" LIKE '%"+searchStr+"%'"
		
		+" OR "+ReceiptlistTB.REMARK_RECEIPTLIST+" LIKE '%"+searchStr+"%'"
		+" OR "+ReceiptlistTB.SHIPNO_RECEIPTLIST+" LIKE '%"+searchStr+"%'";
		
		
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public List<ReceiptlistTB> selectByFlowCode(String flowCode,String kindCode,int beginPageNum,int pageSize){
		String sql = "SELECT * FROM "+ReceiptlistTB.TABLE_NAME+" LEFT JOIN recordlist_flow " +
				" ON recordlist_flow.kindid_recordlist = receiptlist_warehouse.id_receiptlist " +
				" AND recordlist_flow.flowflag_recordlist='1' " +
				" WHERE  recordlist_flow.code_recordlist='"+flowCode+"'" +
				" AND recordlist_flow.kind_recordlist='"+kindCode+"' " +
				" AND " + ReceiptlistTB.STATE_RECEIPTLIST+ "!=9" +
		        " limit "+beginPageNum+","+pageSize; 
		//ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		List<ReceiptlistTB> receiptlistList = selectListBySql(sql);
		return receiptlistList;
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNumForFlowCode(String flowCode,String kindCode){
		
		int rowNum = 0;
		
		String sql = "SELECT count(*) as rowNum FROM "+ReceiptlistTB.TABLE_NAME+" LEFT JOIN recordlist_flow " +
					" ON recordlist_flow.kindid_recordlist = receiptlist_warehouse.id_receiptlist " +
					" AND recordlist_flow.flowflag_recordlist='1' " +
					" AND recordlist_flow.kind_recordlist='"+kindCode+"' " +
					" WHERE  recordlist_flow.code_recordlist='"+flowCode+"'"+
					" AND " + ReceiptlistTB.STATE_RECEIPTLIST+ "!=9";
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public boolean cancel(int id){
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> updateMap = new HashMap<String, Object>();
		
		updateMap.put(ReceiptlistTB.STATE_RECEIPTLIST, ZOZOPOCODE.CANCELED);//过期
		
		updateMap.put(ReceiptlistTB.ISCANCEL_RECEIPTLIST, true);
		
		
		return sqlMachine.updateRow(ReceiptlistTB.TABLE_NAME, updateMap, ReceiptlistTB.ID_RECEIPTLIST, id);
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public boolean updateRemark(int id,String remark){
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> updateMap = new HashMap<String, Object>();
		
		updateMap.put(ReceiptlistTB.REMARK_RECEIPTLIST, remark);
		
		return sqlMachine.updateRow(ReceiptlistTB.TABLE_NAME, updateMap, ReceiptlistTB.ID_RECEIPTLIST, id);
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public boolean updateForShipno(int id,String shipNo){
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> updateMap = new HashMap<String, Object>();
		
		updateMap.put(ReceiptlistTB.SHIPNO_RECEIPTLIST, shipNo);
		
		return sqlMachine.updateRow(ReceiptlistTB.TABLE_NAME, updateMap, ReceiptlistTB.ID_RECEIPTLIST, id);
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public boolean updateForTotal(int id,double total){
		SqlMachine sqlMachine = new SqlMachine(conn);
		HashMap<String, Object> updateMap = new HashMap<String, Object>();
		
		updateMap.put(ReceiptlistTB.TOTAL_RECEIPTLIST, total);
		
		return sqlMachine.updateRow(ReceiptlistTB.TABLE_NAME, updateMap, ReceiptlistTB.ID_RECEIPTLIST, id);
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public double getAveragePorfit(int day){
		
		double num = 0;
		String sql = "select AVG("+ReceiptlistTB.PROFIT_RECEIPTLIST+") as num from " + ReceiptlistTB.TABLE_NAME 
		+ " WHERE " + ReceiptlistTB.ID_RECEIPTLIST 
		+ " IN (" 
		+ " SELECT "+RecordlistTB.KINDID_RECORDLIST+" FROM "+RecordlistTB.TABLE_NAME
		+ " WHERE "+RecordlistTB.KIND_RECORDLIST +" = 'FLOW.WAREHOUSE.RECEIPTLIST'" 
		
		
		+ " AND " + RecordlistTB.NEXTVALUE_RECORDLIST+ "=-1"
		+ " AND " + RecordlistTB.FLOWFLAG_RECORDLIST+ "!=9"
		//+ " limit "+beginPageNum+","+pageSize 
		+ ") "
		+ " AND " + ReceiptlistTB.DATE_RECEIPTLIST +">= DATE_ADD(NOW(), INTERVAL "+day+" DAY)";
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					num = resultSet.getDouble("num");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return num;
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public double getSumPorfit(int day){
		
		double num = 0;
		String sql = "select SUM("+ReceiptlistTB.PROFIT_RECEIPTLIST+") as num from " + ReceiptlistTB.TABLE_NAME 
		+ " WHERE " + ReceiptlistTB.ID_RECEIPTLIST 
		+ " IN (" 
		+ " SELECT "+RecordlistTB.KINDID_RECORDLIST+" FROM "+RecordlistTB.TABLE_NAME
		+ " WHERE "+RecordlistTB.KIND_RECORDLIST +" = 'FLOW.WAREHOUSE.RECEIPTLIST'  " 
		
		+ " AND " + RecordlistTB.NEXTVALUE_RECORDLIST+ "=-1"
		+ " AND " + RecordlistTB.FLOWFLAG_RECORDLIST+ "!=9"
		//+ " limit "+beginPageNum+","+pageSize 
		+ ") "
		+ " AND " + ReceiptlistTB.DATE_RECEIPTLIST +">= DATE_ADD(NOW(), INTERVAL "+day+" DAY)";
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					num = resultSet.getDouble("num");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return num;
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public double getAverageTotal(int day){
		
		double num = 0;
		String sql = "select AVG("+ReceiptlistTB.TOTAL_RECEIPTLIST+") as num from " + ReceiptlistTB.TABLE_NAME 
		+ " WHERE " + ReceiptlistTB.ID_RECEIPTLIST 
		+ " IN (" 
		+ " SELECT "+RecordlistTB.KINDID_RECORDLIST+" FROM "+RecordlistTB.TABLE_NAME
		+ " WHERE "+RecordlistTB.KIND_RECORDLIST +" = 'HOUSEWARE.RECEIPTLIST'  " 
		
		+ " AND " + RecordlistTB.NEXTVALUE_RECORDLIST+ "=-1"
		+ " AND " + RecordlistTB.FLOWFLAG_RECORDLIST+ "!=9"
		//+ " limit "+beginPageNum+","+pageSize 
		+ ") "
		+ " AND " + ReceiptlistTB.DATE_RECEIPTLIST +">= DATE_ADD(NOW(), INTERVAL "+day+" DAY)";
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					num = resultSet.getDouble("num");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return num;
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public double getTotal(int day){
		
		double num = 0;
		String sql = "select SUM("+ReceiptlistTB.TOTAL_RECEIPTLIST+") as num from " + ReceiptlistTB.TABLE_NAME 
		+ " WHERE " + ReceiptlistTB.STATE_RECEIPTLIST  + "!=9"
		
		+ " AND " + ReceiptlistTB.DATE_RECEIPTLIST +">= DATE_ADD(NOW(), INTERVAL "+day+" DAY)";
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					num = resultSet.getDouble("num");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return num;
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public double getTodayTotal(){
		
		double num = 0;
		String sql = "select SUM("+ReceiptlistTB.TOTAL_RECEIPTLIST+") as num from " + ReceiptlistTB.TABLE_NAME 
		+ " WHERE " + ReceiptlistTB.STATE_RECEIPTLIST  + "!=9"
		
		+ " AND TO_DAYS( " + ReceiptlistTB.DATE_RECEIPTLIST +") = TO_DAYS(now())";
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					num = resultSet.getDouble("num");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return num;
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public int getCountNum(int day){
		
		int num = 0;
		String sql = "select COUNT(*) as num from " + ReceiptlistTB.TABLE_NAME 
		+ " WHERE "
	 + ReceiptlistTB.STATE_RECEIPTLIST+ "!=9"
		//+ " limit "+beginPageNum+","+pageSize 
		
		+ " AND " + ReceiptlistTB.DATE_RECEIPTLIST +">= DATE_ADD(NOW(), INTERVAL "+day+" DAY)";
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					num = resultSet.getInt("num");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return num;
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public int getTodayCountNum(){
		
		int num = 0;
		String sql = "select COUNT(*) as num from " + ReceiptlistTB.TABLE_NAME 
		+ " WHERE "
	 + ReceiptlistTB.STATE_RECEIPTLIST+ "!=9"
		//+ " limit "+beginPageNum+","+pageSize 
		
		+ " AND TO_DAYS( " + ReceiptlistTB.DATE_RECEIPTLIST +") = TO_DAYS(now())";
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					num = resultSet.getInt("num");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return num;
	}
	
	/**
	 * @version 1.0
	 * @author Gee
	 */
	public String getDateCountNum(int day){
		
		

		String sql = "SELECT DATE_FORMAT(date_receiptlist, '%m-%d') AS date_receiptlist,COUNT(*) AS num,SUM(total_receiptlist) AS total " +
					"FROM receiptlist_warehouse " +
					"WHERE state_receiptlist!=9 " +
					"AND  date_receiptlist>= DATE_ADD(NOW(), INTERVAL "+day+" DAY) " +
					"GROUP BY TO_DAYS(date_receiptlist)" ;

		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		
		ArrayList<Integer> numList = new ArrayList<Integer>();
		ArrayList<String> dateList = new ArrayList<String>();
		ArrayList<Double> totalList = new ArrayList<Double>();
		if(resultSet != null){
			try {
				while(resultSet.next()){
					int num = resultSet.getInt("num");
					numList.add(num);
					
					String date = resultSet.getString("date_receiptlist");
					dateList.add(date);
					
					double total = resultSet.getDouble("total");
					totalList.add(total);
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		JSONArray numJsonArray = new JSONArray(numList);
		JSONArray dateJsonArray = new JSONArray(dateList);
		JSONArray totalJsonArray = new JSONArray(totalList);
	
		String jsonString = "{\"num\" : " + numJsonArray.toString()
			+",\"date\" : " + dateJsonArray.toString()
			+",\"total\" : " + totalJsonArray.toString()
			+"}";
		
		
		return jsonString;
	}
	
	
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public String computingProfitForReceiptlist(){
		//ReceiptOS receiptOS = new ReceiptOS(conn);
		
		
		String opertionInfo = "";
		/*
		String sql = "SELECT SUM(profit_receipt) AS profit_receipt,receiptlistid_receipt FROM receipt_warehouse GROUP BY receiptlistid_receipt";
		List<ReceiptTB> receiptList = null;
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		
		if(resultSet != null){
				try {
					receiptList = new ArrayList<ReceiptTB>();
					while(resultSet.next()){
						ReceiptTB receipt = new ReceiptTB();
						
						
						receipt.setReceiptlistid_receipt(resultSet.getInt(ReceiptTB.RECEIPTLISTID_RECEIPT));
						receipt.setProfit_receipt(resultSet.getDouble(ReceiptTB.PROFIT_RECEIPT));
						
						receiptList.add(receipt);
					}
					resultSet.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}*/
		RecordlistOS recordlistOS = new RecordlistOS(conn);
		
		Map<Integer,RecordlistTB> recordlistMap = recordlistOS.getRecordlistMapByKindForReport("HOUSEWARE.RECEIPTLIST", 0, 100000);
		
		for(Integer receiptlistid: recordlistMap.keySet()){
			
			ReceiptlistTB receiptlist =	getReceiptlistById(receiptlistid);
			
			RecordlistTB recordlist= recordlistMap.get(receiptlist.getId_receiptlist());
			double shipprice = 0.0;
			if(recordlist!=null){
				shipprice = recordlist.getDoublevalue_recordlist();
			}
			
			if(upadteForProfit(receiptlist,shipprice)){
				opertionInfo +=","+receiptlist.getId_receiptlist();
			}else{
				opertionInfo +=",<font color='red'>"+receiptlist.getId_receiptlist()+"</font>";
				
			}
		}
		
		
	/*	for(ReceiptTB receipt : receiptList){
			
			ReceiptlistTB receiptlist =	getReceiptlistById(receipt.getReceiptlistid_receipt());
		
			receiptlist.setProfit_receiptlist(receipt.getProfit_receipt());
			
			

			RecordlistTB recordlist= recordlistMap.get(receiptlist.getId_receiptlist());
			double shipprice = 0.0;
			if(recordlist!=null){
				shipprice = recordlist.getDoublevalue_recordlist();
			}
			
			if(upadteForProfit(receiptlist,shipprice)){
				opertionInfo +=","+receiptlist.getId_receiptlist();
			}else{
				opertionInfo +=",<font color='red'>"+receiptlist.getId_receiptlist()+"</font>";
				
			}
		}*/
		
		return opertionInfo;
	}
	
	/**
	 * @version 1.0
	 * @author gee
	 */
	public Map<String,Double> getSumPorfitMap(){
		
		String sql = "select DATE_FORMAT(date_receiptlist,'%Y-%m') AS date_receiptlist,SUM("+ReceiptlistTB.PROFIT_RECEIPTLIST+") as num from " + ReceiptlistTB.TABLE_NAME 
		+ " WHERE " + ReceiptlistTB.ID_RECEIPTLIST 
		+ " IN (" 
		+ " SELECT "+RecordlistTB.KINDID_RECORDLIST+" FROM "+RecordlistTB.TABLE_NAME
		+ " WHERE "+RecordlistTB.KIND_RECORDLIST +" = 'FLOW.WAREHOUSE.RECEIPTLIST'  " 
		
		+ " AND " + RecordlistTB.NEXTVALUE_RECORDLIST+ "=-1"
		+ " AND " + RecordlistTB.ISCANCEL_RECORDLIST+ "=false"
		//+ " limit "+beginPageNum+","+pageSize 
		+ ") "
		+ " GROUP BY DATE_FORMAT( " + ReceiptlistTB.DATE_RECEIPTLIST +",'%Y-%m')";
		
	
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		//Flow flow = new Flow(conn);
		Map<String,Double> map = null;
		
		if(resultSet != null){
				try {
					map = new LinkedHashMap<String,Double>();
					while(resultSet.next()){
						map.put(resultSet.getString((ReceiptlistTB.DATE_RECEIPTLIST)), resultSet.getDouble("num"));
					}
					resultSet.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		
		return map;
	}
	
	/**
	 * @version 1.0
	 * @author gee
	 */
	public Map<String,Double> getSalesAmountMap(){
		
		String sql = "select DATE_FORMAT(date_receiptlist,'%Y-%m') AS date_receiptlist,SUM("+ReceiptlistTB.TOTAL_RECEIPTLIST+") as num from " + ReceiptlistTB.TABLE_NAME 
		+ " WHERE " 
		+ ReceiptlistTB.ISCANCEL_RECEIPTLIST + "=false "
		
		+ " GROUP BY DATE_FORMAT( " + ReceiptlistTB.DATE_RECEIPTLIST +",'%Y-%m')";
		
	
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		//Flow flow = new Flow(conn);
		Map<String,Double> map = null;
		
		if(resultSet != null){
				try {
					map = new LinkedHashMap<String,Double>();
					while(resultSet.next()){
						map.put(resultSet.getString((ReceiptlistTB.DATE_RECEIPTLIST)), resultSet.getDouble("num"));
					}
					resultSet.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		
		return map;
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public Map<String,Integer> getCountNumMap(){
		
		
		String sql = "select DATE_FORMAT(date_receiptlist,'%Y-%m') AS date_receiptlist,COUNT(*) as num from " + ReceiptlistTB.TABLE_NAME 
		+ " WHERE "
	 + ReceiptlistTB.ISCANCEL_RECEIPTLIST+ "=false"
		//+ " limit "+beginPageNum+","+pageSize 
		
	 + " GROUP BY DATE_FORMAT( " + ReceiptlistTB.DATE_RECEIPTLIST +",'%Y-%m')";
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		Map<String,Integer> map = null;
		if(resultSet != null){
			try {
				map = new LinkedHashMap<String,Integer>();
				while(resultSet.next()){
					map.put(resultSet.getString((ReceiptlistTB.DATE_RECEIPTLIST)), resultSet.getInt("num"));
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return map;
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public Map<String,Double> getAveragePorfitMap(){
		
		double num = 0;
		String sql = "select DATE_FORMAT(date_receiptlist,'%Y-%m') AS date_receiptlist,AVG("+ReceiptlistTB.PROFIT_RECEIPTLIST+") as num from " + ReceiptlistTB.TABLE_NAME 
		+ " WHERE " +ReceiptlistTB.ISCANCEL_RECEIPTLIST+"=false"
		+ " GROUP BY DATE_FORMAT( " + ReceiptlistTB.DATE_RECEIPTLIST +",'%Y-%m')";
		
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		
		Map<String,Double> map = null;
		if(resultSet != null){
			try {
				map = new LinkedHashMap<String,Double>();
				while(resultSet.next()){
					map.put(resultSet.getString((ReceiptlistTB.DATE_RECEIPTLIST)), resultSet.getDouble("num"));
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return map;
	}
}
